package JDBC_Test.book.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import JDBC_Test.book.JDBCUtil;
import JDBC_Test.book.Model.BookModel;
import JDBC_Test.book.Model.BorrowRecordModel;

public class RecordDao {

	Connection con = null;
	Statement st = null;
	PreparedStatement ps = null;

	/**
	 * 插入借书记录
	 * 
	 * @param borrowRecordModel
	 * @throws SQLException
	 */
	public void insert(BorrowRecordModel borrowRecordModel) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "insert into record (card_no,book_no,state) values(?,?,?)";
		try {
			con = JDBCUtil.geConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, borrowRecordModel.getCardId());
			ps.setString(2, borrowRecordModel.getBookId());

			if (borrowRecordModel.getState() == 0) {
				ps.setString(3, "借书记录");
			} else {
				ps.setString(3, "还书记录");
			}
			ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}

	}

	/**
	 * 动态查询，这里用于查询所有借书记录
	 * 
	 * @param borrowRecordModel
	 * @return
	 * @throws SQLException
	 */
	public List<BorrowRecordModel> selectList(BorrowRecordModel borrowRecordModel) throws SQLException {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("select ");
		sql.append("card_no,book_no,state");
		sql.append(" from record where 1=1");
		List<Object> list = appendWhere(sql, borrowRecordModel);
		List<BorrowRecordModel> result = new ArrayList<BorrowRecordModel>();
		ResultSet rs = null;

		try {
			con = JDBCUtil.geConnection();
			ps = con.prepareStatement(sql.toString());
			//把条件拼接到sql中
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			rs = ps.executeQuery();// 执行sql，获得结果集
			while (rs.next()) {
				BorrowRecordModel b = new BorrowRecordModel();
				b.setBookId(rs.getString("book_no"));
				b.setCardId(rs.getString("card_no"));
				if (rs.getString("state").equals("借书记录"))
					b.setState(0);
				else
					b.setState(1);
				result.add(b);
			}
			for (int i = 0; i < result.size(); i++) {
				System.out.println(result.get(i).toString());
			}

			return result;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}
		return result;
	}

	private List<Object> appendWhere(StringBuffer sql, BorrowRecordModel borrowRecordModel) {
		// TODO Auto-generated method stub
		List<Object> list = new ArrayList<Object>();
		String bookId = borrowRecordModel.getBookId();
//		System.out.println(bookId);
		if (bookId != null) {
			sql.append(" and book_no = ? ");
			list.add(bookId);
		}
		String cardId = borrowRecordModel.getCardId();
		if (cardId != null) {
			sql.append(" and card_no = ?");
			list.add(cardId);
		}

		return list;
	}

	/**
	 * 根据借阅次数查询
	 * 
	 * @throws SQLException
	 */
	public void selectByCount() throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select record.book_no ,book.book_name, count(1) from record join book on book.book_no = record.book_no where state = ? group by record.book_no,book.book_name order by count(1) desc";

		try {
			con = JDBCUtil.geConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, "借书记录");
//			st = con.createStatement();
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String book_no = rs.getString("record.book_no");
				String book_name = rs.getString("book.book_name");
				int count = rs.getInt("count(1)");
				System.out.println("借出次数" + count + "		书号：" + book_no + " 		书名：" + book_name);
//				System.out.println("借出次数"+count);

			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}

	}

}
